create table if not exists jms_dm.dm_outport_bagging_fine_sum_dt(
    sign_date                 date          comment '签收日期'
    , site_code               varchar(100)  COMMENT '站点CODE'
    , site_name               varchar(100)  COMMENT '站点名称'
    , site_type               varchar(100)  COMMENT '站点类型 1-总部,2-代理区,3-加盟商,4-中心,5-集散,6-网点'
    , agent_code              varchar(100)  COMMENT '站点所属代理区CODE'
    , agent_name              varchar(100)  COMMENT '站点所属代理区名称'
    , virt_code               varchar(100)  COMMENT '站点所属虚拟代理区CODE'
    , virt_name               varchar(100)  COMMENT '站点所属虚拟代理区名称'
    , should_bagging_cnt      BIGINT        COMMENT '应建包票数'
    , actual_bagging_cnt      BIGINT        COMMENT '实建包票数'
    , not_bagging_cnt         BIGINT        COMMENT '未建包票数'
    , backfill_bagging_cnt    BIGINT        COMMENT '补建包票数'
    , unbagging_operate_cnt   BIGINT        COMMENT '未建包小件操作票数'
    , not_bagging_fine_amt           decimal(10, 2) COMMENT "未建包罚款"
    , backfill_bagging_reward_amt    decimal(10, 2) COMMENT "补建包奖励"
    , unbagging_operate_reward_amt   decimal(10, 2) COMMENT "未建包操作补贴"
    )
ENGINE=OLAP
DUPLICATE KEY(`sign_date`, `site_code`, `site_name`)
comment '出港建包奖罚汇总'
PARTITION BY RANGE(`sign_date`)
(
    START ("2023-10-15") END ("2023-11-15") EVERY (INTERVAL 1 day)
)
DISTRIBUTED BY HASH(`site_code`) BUCKETS 1
PROPERTIES (
  "replication_num" = "3",
  "dynamic_partition.enable" = "true",
  "dynamic_partition.time_unit" = "DAY",
  "dynamic_partition.time_zone" = "Asia/Shanghai",
  "dynamic_partition.start" = "-185",
  "dynamic_partition.end" = "3",
  "dynamic_partition.prefix" = "p",
  "dynamic_partition.buckets" = "1",
  "in_memory" = "false",
  "storage_format" = "V2",
  "enable_persistent_index" = "false"
);